package cm.tl.pms.util.fileParser.excel;

import cn.hutool.json.JSONUtil;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import static cm.tl.pms.util.fileParser.excel.TemplateProcessor.processTemplate;

/**
 * 久其数据报表 解析处理
 */
@Slf4j
public class ExcelParserByJQ {

    @Data
    @AllArgsConstructor
    @NoArgsConstructor
    public static class ExcelParseTarget {
        private String sheetName;
        private String searchField;
        private int headerIndex;
        private List<Integer> offsetList;
        private List<String> demandNameList;
    }

    @SneakyThrows
    public static void main(String[] args) {
        // Excel文件路径
        String excelFilePath = "C:\\Users\\Lenovo\\Downloads\\173329574810937113.xlsx";
//        List<ExcelParseTarget> targetList = new ArrayList<>();
//        targetList.add(new ExcelParseTarget("CWKB03119JK 资产负债表（接口）", "资  产  总  计", 0, Arrays.asList(2, 3), Arrays.asList("资产总计-期末金额", "资产总计-年初金额")));
//        targetList.add(new ExcelParseTarget("CWKB03119JK 资产负债表（接口）", "流动负债合计", 4, Arrays.asList(2, 3), Arrays.asList("流动负债合计-期末金额", "流动负债合计-年初金额")));
//        targetList.add(new ExcelParseTarget("CWKB03119JK 资产负债表（接口）", "流动资产合计", 0, Arrays.asList(2, 3), Arrays.asList("流动资产合计-期末金额", "流动资产合计-年初金额")));
//        targetList.add(new ExcelParseTarget("CWKB03119JK 资产负债表（接口）", "负债和所有者权益总计", 4, Arrays.asList(2, 3), Arrays.asList("负债和所有者权益总计-期末金额", "负债和所有者权益总计-年初金额")));
//        targetList.add(new ExcelParseTarget("CWKB03219JK 利润表（接口）", "四、利润总额（亏损总额以“－”号填列）", 5, Arrays.asList(2, 3, 4), Arrays.asList("利润总额-本月数", "利润总额-本年累计", "利润总额-上年同期")));
//        targetList.add(new ExcelParseTarget("CWKB03219JK 利润表（接口）", "一、营业总收入", 0, Arrays.asList(2, 3, 4), Arrays.asList("营业总收入-本月数", "营业总收入-本年累计", "营业总收入-上年同期")));
//        targetList.add(new ExcelParseTarget("CWKB03219JK 利润表（接口）", "三、营业利润（亏损以“－”号填列）", 0, Arrays.asList(2, 3, 4), Arrays.asList("营业利润-本月数", "营业利润-本年累计", "营业利润-上年同期")));
        List<ExcelParserByJQ.ExcelParseTarget> targetList = processTemplate(new TemplateProcessor.Request("template_id_3"));
        Map<String, Object> dataMap = parseExcel(new File(excelFilePath), targetList);
        // 打印JSON字符串
        System.out.println(JSONUtil.toJsonStr(dataMap));
    }

    public static Map<String, Object> parseExcel(File file, List<ExcelParseTarget> targetList) throws IOException {
        Map<String, Object> dataMap = new HashMap<>();

        try (FileInputStream fis = new FileInputStream(file);
             Workbook workbook = new XSSFWorkbook(fis)) {
            for (ExcelParseTarget target : targetList) {
                Sheet sheet = workbook.getSheet(target.getSheetName());
                // 这些都是客户给的 格式不固定 测试被迫加的！
                if (sheet == null) {//去掉（接口） 再次尝试读取
                    log.warn(file.getAbsolutePath() + "————sheet: " + target.getSheetName() + " 不存在");
                    if (target.getSheetName().contains("（接口）")) {
                        target.setSheetName(target.getSheetName().replaceAll("（接口）", ""));
                    }
                    sheet = workbook.getSheet(target.getSheetName());
                }

                if (sheet == null) {//去掉 JK/XL 再次尝试读取
                    log.warn(file.getAbsolutePath() + "————sheet: " + target.getSheetName() + " 不存在");
                    if (target.getSheetName().contains("JK") || target.getSheetName().contains("XL")) {
                        target.setSheetName(target.getSheetName().replaceAll("JK", ""));
                        target.setSheetName(target.getSheetName().replaceAll("XL", ""));
                    }
                    sheet = workbook.getSheet(target.getSheetName());
                }

                if (sheet != null) {
                    int targetRowIndex = findRowIndex(sheet, target.getSearchField(), target.getHeaderIndex());
                    if (targetRowIndex != -1) {
                        Row targetRow = sheet.getRow(targetRowIndex);
                        log.info("找到目标行: " + targetRow.getRowNum());
                        for (int i = 0, len = target.getOffsetList().size(); i < len; i++) {
                            int offset = target.getOffsetList().get(i);
                            String demandName = target.getDemandNameList().get(i);
                            dataMap.put(demandName, getCellValue(targetRow.getCell(offset + target.getHeaderIndex())));
                        }
                    }
                } else {
                    log.warn(file.getAbsolutePath() + "————sheet: " + target.getSheetName() + " 不存在");
                }
            }
        }
        return dataMap;
    }

    private static int findRowIndex(Sheet sheet, String targetHeader, int headerIndex) {
        for (Row row : sheet) {
            for (Cell cell : row) {
                if (cell != null && cell.getCellType() == CellType.STRING && cell.getStringCellValue().trim().equals(targetHeader.trim()) &&
                        cell.getColumnIndex() == headerIndex) {
                    log.info("找到头部 header: " + targetHeader + " 所在行数: " + row.getRowNum() + " 列数: " + cell.getColumnIndex());
                    return row.getRowNum();
                }
            }
        }
        return -1;
    }

    private static String getCellValue(Cell cell) {
        if (cell == null) {
            return null;
        }
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                // 将数字单元格转换为字符串
//                cell.setCellType(CellType.STRING);
                DecimalFormat decimalFormat = new DecimalFormat("#.00");
                return decimalFormat.format(cell.getNumericCellValue());
//                return decimalFormat.format(cell.getStringCellValue());
            default:
                return null;
        }
    }
}
